IF OBJECTPROPERTY(OBJECT_ID(N'[Exception]'), N'IsUserTable') = 1
    DROP TABLE [Exception]
GO

CREATE TABLE [Exception]
(
  [ExceptionId]     int                NOT NULL IDENTITY( 1,1 )
, [UtcDate]         datetime           NOT NULL
, [SystemDate]      datetime           NOT NULL
-----------------------------------------------------------------------
, [ErrorContext]    nvarchar  ( 512 )  NOT NULL
, [ErrorNumber]     int                NOT NULL
, [ErrorSeverity]   int                NOT NULL
, [ErrorState]      int                NOT NULL
, [ErrorProcedure]  nvarchar  ( 128 )  NOT NULL
, [ErrorLine]       int                NOT NULL
, [ErrorMessage]    nvarchar  ( max )  NOT NULL
, PRIMARY KEY ([ExceptionId])
)
GO

ALTER TABLE [Exception] ADD CONSTRAINT [DF_Exception_SystemDate] DEFAULT GETDATE() FOR [SystemDate];
ALTER TABLE [Exception] ADD CONSTRAINT [DF_Exception_UtcDate] DEFAULT GETUTCDATE() FOR [UtcDate];
GO

IF OBJECTPROPERTY(OBJECT_ID(N'[ExceptionHandler]'), N'IsProcedure') = 1
    DROP PROCEDURE [ExceptionHandler];
GO
CREATE PROCEDURE [ExceptionHandler]
(
  @ErrorContext    nvarchar  (  512 )  = NULL
, @DatabaseName    nvarchar  (  128 )  = NULL  OUT
, @ErrorProcedure  nvarchar  (  128 )  = NULL  OUT
, @ErrorNumber     int                 = NULL  OUT
, @ErrorSeverity   int                 = NULL  OUT
, @ErrorState      int                 = NULL  OUT
, @ErrorLine       int                 = NULL  OUT
, @ErrorMessage    nvarchar  ( 4000 )  = NULL  OUT
, @ReturnMessage   nvarchar  ( 1000 )  = NULL  OUT
, @ExceptionId     int                 = NULL  OUT
)
AS

--<CommentHeader>
/**********************************************************************************************************************

Properties
=====================================================================================================================
PROCEDURE NAME:		log4.ExceptionHandler
DESCRIPTION:		Returns error info as output parameters and writes info to Exception table
DATE OF ORIGIN:		01-DEC-2006
ORIGINAL AUTHOR:	Greg M. Lucas (data-centric solutions ltd. http://www.data-centric.co.uk)
BUILD DATE:			<--%DbBuildDate%-->
BUILD VERSION:		<--%DbBuildVersion%-->
DEPENDANTS:			Various
DEPENDENCIES:		log4.SessionInfoOutput

Outputs
=====================================================================================================================
Outputs all values collected within the CATCH block plus a formatted error message built from context and error msg

Returns
=====================================================================================================================
- @@ERROR - always zero on success


Additional Notes
=====================================================================================================================
-

Revision history
=====================================================================================================================
ChangeDate		Author	Version		Narrative
============	======	=======		=================================================================================
01-DEC-2006		GML		v0.0.1		Created
------------	------	-------		---------------------------------------------------------------------------------
15-APR-2008		GML		v0.0.3		Now utilises SessionInfoOutput sproc for session values
------------	------	-------		---------------------------------------------------------------------------------

=====================================================================================================================
(C) Copyright 2006-11 data-centric solutions ltd. (http://log4tsql.sourceforge.net/)

This library is free software; you can redistribute it and/or modify it under the terms of the
GNU Lesser General Public License as published by the Free Software Foundation (www.fsf.org);
either version 3.0 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this
library; if not, you can find it at http://www.opensource.org/licenses/lgpl-3.0.html
or http://www.gnu.org/licenses/lgpl.html

**********************************************************************************************************************/
--</CommentHeader>

BEGIN
    SET NOCOUNT ON;

    --! Make sure don't have any NULLs
    SET @ErrorContext    = COALESCE(@ErrorContext, '');
    SET @DatabaseName    = COALESCE(@DatabaseName, DB_NAME());
    SET @ErrorProcedure  = COALESCE(NULLIF(@ErrorProcedure, ''), ERROR_PROCEDURE(), '');
    SET @ErrorNumber     = COALESCE(ERROR_NUMBER(), 0);
    SET @ErrorSeverity   = COALESCE(ERROR_SEVERITY(), 0);
    SET @ErrorState      = COALESCE(ERROR_STATE(), 0);
    SET @ErrorLine       = COALESCE(ERROR_LINE(), 0);
    SET @ErrorMessage    = COALESCE(ERROR_MESSAGE()
                             , 'ERROR_MESSAGE() Not Found for @@ERROR: '
                                 + COALESCE(CAST(ERROR_NUMBER() AS varchar(16)), 'NULL'));

    --! Generate a detailed, nicely formatted error message to return to the caller
	DECLARE @context nvarchar(512); SET @context = COALESCE(NULLIF(@ErrorContext, '') + ' due to ', 'ERROR! ');
	SET @ReturnMessage	= @context
						+ CASE
							WHEN LEN(ERROR_MESSAGE()) > (994 - LEN(@context))
								THEN '"' + SUBSTRING(@ErrorMessage, 1, (994 - LEN(@context))) + '..."'
							ELSE
								'"' + @ErrorMessage + '"'
						  END;

    --! Working variables
    DECLARE @tblExceptionId  table  (ExceptionId int NOT NULL UNIQUE);

    --! Record what we have
    INSERT [Exception]
    (
      [ErrorContext]
    , [ErrorNumber]
    , [ErrorSeverity]
    , [ErrorState]
    , [ErrorProcedure]
    , [ErrorLine]
    , [ErrorMessage]
    )
    OUTPUT inserted.ExceptionId INTO @tblExceptionId
    VALUES
    (
      @ErrorContext
    , @ErrorNumber
    , @ErrorSeverity
    , @ErrorState
    , @ErrorProcedure
    , @ErrorLine
    , @ErrorMessage
    );

    SELECT @ExceptionId = ExceptionId FROM @tblExceptionId;

    SET NOCOUNT OFF;

    RETURN;
END
GO

IF OBJECTPROPERTY(OBJECT_ID(N'[HOWTO_SimpleExample]'), N'IsProcedure') = 1
    DROP PROCEDURE [HOWTO_SimpleExample];
GO
CREATE PROCEDURE [HOWTO_SimpleExample]
(
  @dividend float   = 1.6180339887
, @devisor  float   = 0
, @result   float   = NULL           OUT
)
AS

--<CommentHeader>
/**********************************************************************************************************************
(C) Copyright 2006-11 data-centric solutions ltd. (http://log4tsql.sourceforge.net/)

This library is free software; you can redistribute it and/or modify it under the terms of the
GNU Lesser General Public License as published by the Free Software Foundation (www.fsf.org);
either version 3.0 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this
library; if not, you can find it at http://www.opensource.org/licenses/lgpl-3.0.html
or http://www.gnu.org/licenses/lgpl.html
**********************************************************************************************************************/
--</CommentHeader>

BEGIN
    --! Make sure that we do actually get an error on divide by zero
    SET ANSI_WARNINGS ON;
    SET ARITHABORT ON;

    BEGIN TRY

        --! Get the result to output
        SET @result = @dividend / @devisor;

    END TRY
    BEGIN CATCH
        IF ABS(XACT_STATE()) = 1 OR @@TRANCOUNT > 0 ROLLBACK TRAN;

        EXEC ExceptionHandler;
    END CATCH

    RETURN;
END
GO

IF OBJECTPROPERTY(OBJECT_ID(N'[HOWTO_DetailedExample]'), N'IsProcedure') = 1
    DROP PROCEDURE [HOWTO_DetailedExample];
GO
CREATE PROCEDURE [HOWTO_DetailedExample]
(
  @dividend float   = 1.6180339887
, @devisor  float   = 0
, @result   float   = NULL           OUT
)
AS

--<CommentHeader>
/**********************************************************************************************************************
(C) Copyright 2006-11 data-centric solutions ltd. (http://log4tsql.sourceforge.net/)

This library is free software; you can redistribute it and/or modify it under the terms of the
GNU Lesser General Public License as published by the Free Software Foundation (www.fsf.org);
either version 3.0 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this
library; if not, you can find it at http://www.opensource.org/licenses/lgpl-3.0.html
or http://www.gnu.org/licenses/lgpl.html
**********************************************************************************************************************/
--</CommentHeader>

BEGIN
    SET NOCOUNT ON

    --! Standard/ExceptionHandler variables
    DECLARE @_Error         int
          , @_Step          nvarchar  (  128 )
          , @_Message       nvarchar  (  512 )
          , @_ErrorContext  nvarchar  (  512 )

    SET @_Error = 0;

    BEGIN TRY
        SET @_Step = 'Validate inputs';

        IF @dividend IS NULL
            BEGIN
                SET @_Message = 'Found invalid Dividend: ' + COALESCE(CAST(@dividend AS varchar(16)), 'NULL');
                RAISERROR(@_Message, 16, 1);
            END

        IF @devisor IS NULL
            BEGIN
                SET @_Message = 'Found invalid Divisor: ' + COALESCE(CAST(@devisor AS varchar(16)), 'NULL');
                RAISERROR(@_Message, 16, 2);
            END

        SET @_Step = 'Calculate output value';

        --! Do the business logic
        SET @result = @dividend / @devisor;
    END TRY
    BEGIN CATCH
        SET @_ErrorContext = 'Failed to demonstrate detailed exception handling'
                           + ' at step: ' + COALESCE('[' + @_Step + ']', 'NULL')
                           + ' for dividend: ' + COALESCE(CAST(@dividend AS varchar(16)), 'NULL')
                           + ' and devisor: ' + COALESCE(CAST(@devisor AS varchar(16)), 'NULL')

        IF (XACT_STATE() = -1) -- uncommitable transaction
        OR (@@TRANCOUNT > 0 AND XACT_STATE() != 1) -- undocumented uncommitable transaction
            BEGIN
                ROLLBACK TRAN;
                SET @_ErrorContext = @_ErrorContext + ' (Forced rolled back of all changes)';
            END

        EXEC ExceptionHandler
                  @ErrorContext  = @_ErrorContext
                , @ErrorNumber   = @_Error OUT
                , @ReturnMessage = @_Message OUT
        ;
    END CATCH

--/////////////////////////////////////////////////////////////////////////////////////////////////
OnComplete:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    --! Finally, throw an exception that will be detected by the caller
    IF @_Error > 0
        RAISERROR(@_Message, 16, 99);

    SET NOCOUNT OFF;

    --! Return the value of @@ERROR (which will be zero on success)
    RETURN (@_Error);
END
GO
